IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.VIEWS
           WHERE TABLE_NAME='vBillDetail'
           AND TABLE_SCHEMA='dbo'
          )
          DROP VIEW dbo.vBillDetail
GO

CREATE VIEW dbo.vBillDetail
AS
/*@@*_____________________________________________________________________


_____________________________________________________________________*@@*/

  SELECT
    BillDetail.Id,
    BillDetail.ProductId,
    BillDetail.Amount,
    BillDetail.SalePrice,
    BillDetail.BillId,
    BillDetail.WarrantyTime,
    BillDetail.ProductPrice,
    TotalProductPrice = BillDetail.Amount * BillDetail.ProductPrice,
    TotalSalePrice = BillDetail.Amount * BillDetail.SalePrice,
    TotalInputPrice = SUM(ISNULL(BillDetail_InProdDetail.Amount, 0) * ISNULL(InOutProdDetail.InputPrice, 0)),
    TotalInputAmount = SUM(CASE
								WHEN BillDetail_InProdDetail.InProdDetailId IS NOT NULL THEN BillDetail_InProdDetail.Amount
								ELSE 0
							END)
  FROM
    BillDetail
    
    LEFT JOIN BillDetail_InProdDetail
		ON BillDetail.Id = BillDetail_InProdDetail.BillDetailId
	
	LEFT JOIN InOutProdDetail
		ON BillDetail_InProdDetail.InProdDetailId = InOutProdDetail.Id
		
	GROUP BY BillDetail.Id,
		BillDetail.ProductId,
		BillDetail.Amount,
		BillDetail.SalePrice,
		BillDetail.BillId,
		BillDetail.WarrantyTime,
		BillDetail.ProductPrice